-- ============================================
-- 月老APP 通用数据库设计
-- 版本: 3.3
-- 创建日期: 2025-10-25
-- 说明: 通用版本，不使用 PostgreSQL 特定的枚举类型
--       使用 VARCHAR + CHECK 约束代替，兼容多种数据库
-- 更新: 移除所有枚举类型，使用字符串类型代替
-- ============================================

-- 设置客户端编码（PostgreSQL）
SET client_encoding = 'UTF8';

-- 启用PostGIS扩展（用于地理空间查询，仅 PostgreSQL）
CREATE EXTENSION IF NOT EXISTS postgis;

-- ============================================
-- 1. 用户相关表
-- ============================================

-- 用户基础信息表
DROP TABLE IF EXISTS users CASCADE;
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  phone VARCHAR(20) UNIQUE,
  password VARCHAR(255),
  nickname VARCHAR(50),
  real_name VARCHAR(50),
  gender VARCHAR(20),
  birthday DATE,
  age INTEGER,
  avatar VARCHAR(500),
  email VARCHAR(100),
  wechat_openid VARCHAR(100) UNIQUE,
  wechat_unionid VARCHAR(100) UNIQUE,
  qq_openid VARCHAR(100) UNIQUE,
  wechat VARCHAR(50),
  qq VARCHAR(20),
  is_vip BOOLEAN NOT NULL DEFAULT FALSE,
  vip_plan_id BIGINT,
  vip_expire_time TIMESTAMP,
  status SMALLINT NOT NULL DEFAULT 1 CHECK (status IN (0, 1, 2)),
  is_completed BOOLEAN NOT NULL DEFAULT FALSE,
  last_login_time TIMESTAMP,
  last_login_ip VARCHAR(50),
  last_login_location GEOGRAPHY(POINT, 4326),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT check_users_gender CHECK (gender IN ('male', 'female', 'other'))
);

-- 创建索引
CREATE INDEX idx_users_nickname ON users(nickname);
CREATE INDEX idx_users_gender_age ON users(gender, age);
CREATE INDEX idx_users_is_vip ON users(is_vip);
CREATE INDEX idx_users_vip_plan_id ON users(vip_plan_id);
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_users_created_at ON users(created_at);
-- 为地理位置字段创建空间索引（GIST索引，用于高效查询附近的人）
CREATE INDEX idx_users_last_login_location ON users USING GIST(last_login_location);

-- 注释
COMMENT ON TABLE users IS '用户基础信息表';
COMMENT ON COLUMN users.id IS '用户ID';
COMMENT ON COLUMN users.phone IS '手机号';
COMMENT ON COLUMN users.password IS '密码（加密）';
COMMENT ON COLUMN users.nickname IS '昵称';
COMMENT ON COLUMN users.real_name IS '真实姓名';
COMMENT ON COLUMN users.gender IS '性别: male-男, female-女, other-其他';
COMMENT ON COLUMN users.birthday IS '生日';
COMMENT ON COLUMN users.age IS '年龄（计算字段）';
COMMENT ON COLUMN users.avatar IS '头像URL';
COMMENT ON COLUMN users.email IS '邮箱';
COMMENT ON COLUMN users.wechat_openid IS '微信openid';
COMMENT ON COLUMN users.wechat_unionid IS '微信unionid';
COMMENT ON COLUMN users.qq_openid IS 'QQ openid';
COMMENT ON COLUMN users.wechat IS '微信号';
COMMENT ON COLUMN users.qq IS 'QQ号';
COMMENT ON COLUMN users.is_vip IS '是否VIP';
COMMENT ON COLUMN users.vip_expire_time IS 'VIP过期时间';
COMMENT ON COLUMN users.status IS '账号状态: 0-禁用, 1-正常, 2-冻结';
COMMENT ON COLUMN users.is_completed IS '是否完成引导';
COMMENT ON COLUMN users.last_login_time IS '最后登录时间';
COMMENT ON COLUMN users.last_login_ip IS '最后登录IP';
COMMENT ON COLUMN users.last_login_location IS '最后登录地理位置（GEOGRAPHY类型，存储经纬度点，用于查找附近的人）';
COMMENT ON COLUMN users.created_at IS '创建时间';
COMMENT ON COLUMN users.updated_at IS '更新时间';

-- 用户详细资料表
DROP TABLE IF EXISTS user_profiles CASCADE;
CREATE TABLE user_profiles (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL UNIQUE,
  hometown_province VARCHAR(50),
  hometown_city VARCHAR(50),
  hometown_district VARCHAR(50),
  residence_province VARCHAR(50),
  residence_city VARCHAR(50),
  residence_district VARCHAR(50),
  height INTEGER,
  weight INTEGER,
  education VARCHAR(50),
  school VARCHAR(100),
  occupation VARCHAR(100),
  company VARCHAR(100),
  income VARCHAR(50),
  house VARCHAR(50),
  car VARCHAR(50),
  marital_status VARCHAR(20),
  has_children BOOLEAN,
  introduction TEXT,
  requirements TEXT,
  hobbies JSONB,
  purposes JSONB,
  tags JSONB,
  constellation VARCHAR(20),
  blood_type VARCHAR(10),
  smoking VARCHAR(20),
  drinking VARCHAR(20),
  show_clear_image BOOLEAN NOT NULL DEFAULT TRUE,
  profile_completion INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_user_profiles_user_id ON user_profiles(user_id);
CREATE INDEX idx_user_profiles_residence ON user_profiles(residence_province, residence_city);
CREATE INDEX idx_user_profiles_education ON user_profiles(education);
CREATE INDEX idx_user_profiles_income ON user_profiles(income);

COMMENT ON TABLE user_profiles IS '用户详细资料表';
COMMENT ON COLUMN user_profiles.id IS '资料ID';
COMMENT ON COLUMN user_profiles.user_id IS '用户ID';
COMMENT ON COLUMN user_profiles.hometown_province IS '家乡省份';
COMMENT ON COLUMN user_profiles.hometown_city IS '家乡城市';
COMMENT ON COLUMN user_profiles.hometown_district IS '家乡区县';
COMMENT ON COLUMN user_profiles.residence_province IS '居住省份';
COMMENT ON COLUMN user_profiles.residence_city IS '居住城市';
COMMENT ON COLUMN user_profiles.residence_district IS '居住区县';
COMMENT ON COLUMN user_profiles.height IS '身高（cm）';
COMMENT ON COLUMN user_profiles.weight IS '体重（kg）';
COMMENT ON COLUMN user_profiles.education IS '学历';
COMMENT ON COLUMN user_profiles.school IS '毕业学校';
COMMENT ON COLUMN user_profiles.occupation IS '职业';
COMMENT ON COLUMN user_profiles.company IS '公司';
COMMENT ON COLUMN user_profiles.income IS '收入范围';
COMMENT ON COLUMN user_profiles.house IS '房产情况';
COMMENT ON COLUMN user_profiles.car IS '车产情况';
COMMENT ON COLUMN user_profiles.marital_status IS '婚姻状况';
COMMENT ON COLUMN user_profiles.has_children IS '是否有孩子';
COMMENT ON COLUMN user_profiles.introduction IS '个人介绍';
COMMENT ON COLUMN user_profiles.requirements IS '择偶要求';
COMMENT ON COLUMN user_profiles.hobbies IS '兴趣爱好（数组）';
COMMENT ON COLUMN user_profiles.purposes IS '交友目的（数组）';
COMMENT ON COLUMN user_profiles.tags IS '个人标签（数组）';
COMMENT ON COLUMN user_profiles.constellation IS '星座';
COMMENT ON COLUMN user_profiles.blood_type IS '血型';
COMMENT ON COLUMN user_profiles.smoking IS '吸烟情况';
COMMENT ON COLUMN user_profiles.drinking IS '饮酒情况';
COMMENT ON COLUMN user_profiles.show_clear_image IS '是否展示高清照片';
COMMENT ON COLUMN user_profiles.profile_completion IS '资料完整度（百分比）';
COMMENT ON COLUMN user_profiles.created_at IS '创建时间';
COMMENT ON COLUMN user_profiles.updated_at IS '更新时间';

-- 用户相册表
DROP TABLE IF EXISTS user_albums CASCADE;
CREATE TABLE user_albums (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  image_url VARCHAR(500) NOT NULL,
  sort_order INTEGER NOT NULL DEFAULT 0,
  is_cover BOOLEAN NOT NULL DEFAULT FALSE,
  status SMALLINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_user_albums_user_id ON user_albums(user_id);
CREATE INDEX idx_user_albums_sort_order ON user_albums(sort_order);

COMMENT ON TABLE user_albums IS '用户相册表';
COMMENT ON COLUMN user_albums.id IS '相册ID';
COMMENT ON COLUMN user_albums.user_id IS '用户ID';
COMMENT ON COLUMN user_albums.image_url IS '图片URL';
COMMENT ON COLUMN user_albums.sort_order IS '排序';
COMMENT ON COLUMN user_albums.is_cover IS '是否封面图';
COMMENT ON COLUMN user_albums.status IS '状态: 0-删除, 1-正常';
COMMENT ON COLUMN user_albums.created_at IS '创建时间';

-- 用户设置表
DROP TABLE IF EXISTS user_settings CASCADE;
CREATE TABLE user_settings (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL UNIQUE,
  notification_like BOOLEAN NOT NULL DEFAULT TRUE,
  notification_match BOOLEAN NOT NULL DEFAULT TRUE,
  notification_message BOOLEAN NOT NULL DEFAULT TRUE,
  notification_comment BOOLEAN NOT NULL DEFAULT TRUE,
  notification_system BOOLEAN NOT NULL DEFAULT TRUE,
  privacy_show_age BOOLEAN NOT NULL DEFAULT TRUE,
  privacy_show_location BOOLEAN NOT NULL DEFAULT TRUE,
  privacy_show_online BOOLEAN NOT NULL DEFAULT TRUE,
  privacy_allow_visitor BOOLEAN NOT NULL DEFAULT TRUE,
  privacy_allow_stranger_message BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_user_settings_user_id ON user_settings(user_id);

COMMENT ON TABLE user_settings IS '用户设置表';
COMMENT ON COLUMN user_settings.id IS '设置ID';
COMMENT ON COLUMN user_settings.user_id IS '用户ID';
COMMENT ON COLUMN user_settings.notification_like IS '喜欢通知';
COMMENT ON COLUMN user_settings.notification_match IS '配对通知';
COMMENT ON COLUMN user_settings.notification_message IS '消息通知';
COMMENT ON COLUMN user_settings.notification_comment IS '评论通知';
COMMENT ON COLUMN user_settings.notification_system IS '系统通知';
COMMENT ON COLUMN user_settings.privacy_show_age IS '显示年龄';
COMMENT ON COLUMN user_settings.privacy_show_location IS '显示位置';
COMMENT ON COLUMN user_settings.privacy_show_online IS '显示在线状态';
COMMENT ON COLUMN user_settings.privacy_allow_visitor IS '允许访客';
COMMENT ON COLUMN user_settings.privacy_allow_stranger_message IS '允许陌生人消息';
COMMENT ON COLUMN user_settings.created_at IS '创建时间';
COMMENT ON COLUMN user_settings.updated_at IS '更新时间';

-- 用户黑名单表
DROP TABLE IF EXISTS user_blacklist CASCADE;
CREATE TABLE user_blacklist (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  blocked_user_id BIGINT NOT NULL,
  reason VARCHAR(255),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(user_id, blocked_user_id)
);

CREATE INDEX idx_user_blacklist_user_id ON user_blacklist(user_id);
CREATE INDEX idx_user_blacklist_blocked_user_id ON user_blacklist(blocked_user_id);

COMMENT ON TABLE user_blacklist IS '用户黑名单表';
COMMENT ON COLUMN user_blacklist.id IS '黑名单ID';
COMMENT ON COLUMN user_blacklist.user_id IS '用户ID';
COMMENT ON COLUMN user_blacklist.blocked_user_id IS '被拉黑用户ID';
COMMENT ON COLUMN user_blacklist.reason IS '拉黑原因';
COMMENT ON COLUMN user_blacklist.created_at IS '创建时间';

-- ============================================
-- 2. 用户关系表
-- ============================================

-- 用户喜欢关系表
DROP TABLE IF EXISTS user_likes CASCADE;
CREATE TABLE user_likes (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  liked_user_id BIGINT NOT NULL,
  is_super BOOLEAN NOT NULL DEFAULT FALSE,
  is_mutual BOOLEAN NOT NULL DEFAULT FALSE,
  status SMALLINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(user_id, liked_user_id)
);

CREATE INDEX idx_user_likes_user_id ON user_likes(user_id);
CREATE INDEX idx_user_likes_liked_user_id ON user_likes(liked_user_id);
CREATE INDEX idx_user_likes_is_mutual ON user_likes(is_mutual);
CREATE INDEX idx_user_likes_created_at ON user_likes(created_at);

COMMENT ON TABLE user_likes IS '用户喜欢关系表';
COMMENT ON COLUMN user_likes.id IS '喜欢ID';
COMMENT ON COLUMN user_likes.user_id IS '用户ID（喜欢者）';
COMMENT ON COLUMN user_likes.liked_user_id IS '被喜欢用户ID';
COMMENT ON COLUMN user_likes.is_super IS '是否超级喜欢';
COMMENT ON COLUMN user_likes.is_mutual IS '是否互相喜欢（配对）';
COMMENT ON COLUMN user_likes.status IS '状态: 0-取消, 1-有效';
COMMENT ON COLUMN user_likes.created_at IS '创建时间';
COMMENT ON COLUMN user_likes.updated_at IS '更新时间';

-- 用户关注关系表
DROP TABLE IF EXISTS user_follows CASCADE;
CREATE TABLE user_follows (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  followed_user_id BIGINT NOT NULL,
  is_mutual BOOLEAN NOT NULL DEFAULT FALSE,
  status SMALLINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(user_id, followed_user_id)
);

CREATE INDEX idx_user_follows_user_id ON user_follows(user_id);
CREATE INDEX idx_user_follows_followed_user_id ON user_follows(followed_user_id);
CREATE INDEX idx_user_follows_is_mutual ON user_follows(is_mutual);
CREATE INDEX idx_user_follows_created_at ON user_follows(created_at);

COMMENT ON TABLE user_follows IS '用户关注关系表';
COMMENT ON COLUMN user_follows.id IS '关注ID';
COMMENT ON COLUMN user_follows.user_id IS '用户ID（关注者）';
COMMENT ON COLUMN user_follows.followed_user_id IS '被关注用户ID';
COMMENT ON COLUMN user_follows.is_mutual IS '是否互相关注';
COMMENT ON COLUMN user_follows.status IS '状态: 0-取消, 1-有效';
COMMENT ON COLUMN user_follows.created_at IS '创建时间';
COMMENT ON COLUMN user_follows.updated_at IS '更新时间';

-- 用户配对表
DROP TABLE IF EXISTS user_matches CASCADE;
CREATE TABLE user_matches (
  id BIGSERIAL PRIMARY KEY,
  user_id_1 BIGINT NOT NULL,
  user_id_2 BIGINT NOT NULL,
  is_super_match BOOLEAN NOT NULL DEFAULT FALSE,
  status SMALLINT NOT NULL DEFAULT 1,
  matched_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  unmatched_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(user_id_1, user_id_2)
);

CREATE INDEX idx_user_matches_user_id_1 ON user_matches(user_id_1);
CREATE INDEX idx_user_matches_user_id_2 ON user_matches(user_id_2);
CREATE INDEX idx_user_matches_matched_at ON user_matches(matched_at);

COMMENT ON TABLE user_matches IS '用户配对表';
COMMENT ON COLUMN user_matches.id IS '配对ID';
COMMENT ON COLUMN user_matches.user_id_1 IS '用户ID 1';
COMMENT ON COLUMN user_matches.user_id_2 IS '用户ID 2';
COMMENT ON COLUMN user_matches.is_super_match IS '是否超级配对';
COMMENT ON COLUMN user_matches.status IS '状态: 0-解除, 1-有效';
COMMENT ON COLUMN user_matches.matched_at IS '配对时间';
COMMENT ON COLUMN user_matches.unmatched_at IS '解除配对时间';
COMMENT ON COLUMN user_matches.created_at IS '创建时间';
COMMENT ON COLUMN user_matches.updated_at IS '更新时间';

-- 访客记录表
DROP TABLE IF EXISTS user_visitors CASCADE;
CREATE TABLE user_visitors (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  visitor_id BIGINT NOT NULL,
  visit_count INTEGER NOT NULL DEFAULT 1,
  last_visit_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(user_id, visitor_id)
);

CREATE INDEX idx_user_visitors_user_id ON user_visitors(user_id);
CREATE INDEX idx_user_visitors_visitor_id ON user_visitors(visitor_id);
CREATE INDEX idx_user_visitors_last_visit_at ON user_visitors(last_visit_at);

COMMENT ON TABLE user_visitors IS '访客记录表';
COMMENT ON COLUMN user_visitors.id IS '访客ID';
COMMENT ON COLUMN user_visitors.user_id IS '被访问用户ID';
COMMENT ON COLUMN user_visitors.visitor_id IS '访客用户ID';
COMMENT ON COLUMN user_visitors.visit_count IS '访问次数';
COMMENT ON COLUMN user_visitors.last_visit_at IS '最后访问时间';
COMMENT ON COLUMN user_visitors.created_at IS '创建时间';

-- ============================================
-- 3. 话题与动态表
-- ============================================

-- 话题表
DROP TABLE IF EXISTS topics CASCADE;
CREATE TABLE topics (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  description VARCHAR(500),
  cover_image VARCHAR(500),
  post_count INTEGER NOT NULL DEFAULT 0,
  member_count INTEGER NOT NULL DEFAULT 0,
  view_count INTEGER NOT NULL DEFAULT 0,
  sort_order INTEGER NOT NULL DEFAULT 0,
  is_hot BOOLEAN NOT NULL DEFAULT FALSE,
  status SMALLINT NOT NULL DEFAULT 1,
  created_by BIGINT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_topics_post_count ON topics(post_count);
CREATE INDEX idx_topics_member_count ON topics(member_count);
CREATE INDEX idx_topics_is_hot ON topics(is_hot);
CREATE INDEX idx_topics_status ON topics(status);

COMMENT ON TABLE topics IS '话题表';
COMMENT ON COLUMN topics.id IS '话题ID';
COMMENT ON COLUMN topics.name IS '话题名称';
COMMENT ON COLUMN topics.description IS '话题描述';
COMMENT ON COLUMN topics.cover_image IS '封面图';
COMMENT ON COLUMN topics.post_count IS '帖子数量';
COMMENT ON COLUMN topics.member_count IS '成员数量';
COMMENT ON COLUMN topics.view_count IS '浏览次数';
COMMENT ON COLUMN topics.sort_order IS '排序';
COMMENT ON COLUMN topics.is_hot IS '是否热门';
COMMENT ON COLUMN topics.status IS '状态: 0-禁用, 1-正常';
COMMENT ON COLUMN topics.created_by IS '创建者ID';
COMMENT ON COLUMN topics.created_at IS '创建时间';
COMMENT ON COLUMN topics.updated_at IS '更新时间';

-- 话题成员表
DROP TABLE IF EXISTS topic_members CASCADE;
CREATE TABLE topic_members (
  id BIGSERIAL PRIMARY KEY,
  topic_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  post_count INTEGER NOT NULL DEFAULT 0,
  joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(topic_id, user_id)
);

CREATE INDEX idx_topic_members_topic_id ON topic_members(topic_id);
CREATE INDEX idx_topic_members_user_id ON topic_members(user_id);

COMMENT ON TABLE topic_members IS '话题成员表';
COMMENT ON COLUMN topic_members.id IS '成员ID';
COMMENT ON COLUMN topic_members.topic_id IS '话题ID';
COMMENT ON COLUMN topic_members.user_id IS '用户ID';
COMMENT ON COLUMN topic_members.post_count IS '发帖数';
COMMENT ON COLUMN topic_members.joined_at IS '加入时间';

-- 动态/帖子表
DROP TABLE IF EXISTS posts CASCADE;
CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  topic_id BIGINT,
  content TEXT NOT NULL,
  images JSONB,
  video_url VARCHAR(500),
  location VARCHAR(200),
  latitude DECIMAL(10,7),
  longitude DECIMAL(10,7),
  like_count INTEGER NOT NULL DEFAULT 0,
  comment_count INTEGER NOT NULL DEFAULT 0,
  share_count INTEGER NOT NULL DEFAULT 0,
  view_count INTEGER NOT NULL DEFAULT 0,
  is_top BOOLEAN NOT NULL DEFAULT FALSE,
  is_hot BOOLEAN NOT NULL DEFAULT FALSE,
  status SMALLINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_topic_id ON posts(topic_id);
CREATE INDEX idx_posts_location ON posts(latitude, longitude);
CREATE INDEX idx_posts_like_count ON posts(like_count);
CREATE INDEX idx_posts_is_hot ON posts(is_hot);
CREATE INDEX idx_posts_created_at ON posts(created_at);

COMMENT ON TABLE posts IS '动态/帖子表';
COMMENT ON COLUMN posts.id IS '帖子ID';
COMMENT ON COLUMN posts.user_id IS '用户ID';
COMMENT ON COLUMN posts.topic_id IS '话题ID';
COMMENT ON COLUMN posts.content IS '内容';
COMMENT ON COLUMN posts.images IS '图片数组';
COMMENT ON COLUMN posts.video_url IS '视频URL';
COMMENT ON COLUMN posts.location IS '位置';
COMMENT ON COLUMN posts.latitude IS '纬度';
COMMENT ON COLUMN posts.longitude IS '经度';
COMMENT ON COLUMN posts.like_count IS '点赞数';
COMMENT ON COLUMN posts.comment_count IS '评论数';
COMMENT ON COLUMN posts.share_count IS '分享数';
COMMENT ON COLUMN posts.view_count IS '浏览数';
COMMENT ON COLUMN posts.is_top IS '是否置顶';
COMMENT ON COLUMN posts.is_hot IS '是否热门';
COMMENT ON COLUMN posts.status IS '状态: 0-删除, 1-正常, 2-审核中, 3-未通过';
COMMENT ON COLUMN posts.created_at IS '创建时间';
COMMENT ON COLUMN posts.updated_at IS '更新时间';

-- 帖子点赞表
DROP TABLE IF EXISTS post_likes CASCADE;
CREATE TABLE post_likes (
  id BIGSERIAL PRIMARY KEY,
  post_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(post_id, user_id)
);

CREATE INDEX idx_post_likes_post_id ON post_likes(post_id);
CREATE INDEX idx_post_likes_user_id ON post_likes(user_id);
CREATE INDEX idx_post_likes_created_at ON post_likes(created_at);

COMMENT ON TABLE post_likes IS '帖子点赞表';
COMMENT ON COLUMN post_likes.id IS '点赞ID';
COMMENT ON COLUMN post_likes.post_id IS '帖子ID';
COMMENT ON COLUMN post_likes.user_id IS '用户ID';
COMMENT ON COLUMN post_likes.created_at IS '创建时间';

-- 评论表
DROP TABLE IF EXISTS comments CASCADE;
CREATE TABLE comments (
  id BIGSERIAL PRIMARY KEY,
  post_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  parent_id BIGINT,
  reply_to_user_id BIGINT,
  content TEXT NOT NULL,
  images JSONB,
  like_count INTEGER NOT NULL DEFAULT 0,
  reply_count INTEGER NOT NULL DEFAULT 0,
  status SMALLINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_comments_post_id ON comments(post_id);
CREATE INDEX idx_comments_user_id ON comments(user_id);
CREATE INDEX idx_comments_parent_id ON comments(parent_id);
CREATE INDEX idx_comments_created_at ON comments(created_at);

COMMENT ON TABLE comments IS '评论表';
COMMENT ON COLUMN comments.id IS '评论ID';
COMMENT ON COLUMN comments.post_id IS '帖子ID';
COMMENT ON COLUMN comments.user_id IS '用户ID';
COMMENT ON COLUMN comments.parent_id IS '父评论ID（回复）';
COMMENT ON COLUMN comments.reply_to_user_id IS '回复目标用户ID';
COMMENT ON COLUMN comments.content IS '评论内容';
COMMENT ON COLUMN comments.images IS '图片数组';
COMMENT ON COLUMN comments.like_count IS '点赞数';
COMMENT ON COLUMN comments.reply_count IS '回复数';
COMMENT ON COLUMN comments.status IS '状态: 0-删除, 1-正常';
COMMENT ON COLUMN comments.created_at IS '创建时间';
COMMENT ON COLUMN comments.updated_at IS '更新时间';

-- 评论点赞表
DROP TABLE IF EXISTS comment_likes CASCADE;
CREATE TABLE comment_likes (
  id BIGSERIAL PRIMARY KEY,
  comment_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(comment_id, user_id)
);

CREATE INDEX idx_comment_likes_comment_id ON comment_likes(comment_id);
CREATE INDEX idx_comment_likes_user_id ON comment_likes(user_id);

COMMENT ON TABLE comment_likes IS '评论点赞表';
COMMENT ON COLUMN comment_likes.id IS '点赞ID';
COMMENT ON COLUMN comment_likes.comment_id IS '评论ID';
COMMENT ON COLUMN comment_likes.user_id IS '用户ID';
COMMENT ON COLUMN comment_likes.created_at IS '创建时间';

-- ============================================
-- 4. 群聊与消息表
-- ============================================

-- 群聊表
DROP TABLE IF EXISTS groups CASCADE;
CREATE TABLE groups (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  avatar VARCHAR(500),
  description TEXT,
  announcement TEXT,
  owner_id BIGINT NOT NULL,
  member_count INTEGER NOT NULL DEFAULT 0,
  max_member_count INTEGER NOT NULL DEFAULT 500,
  is_mute_all BOOLEAN NOT NULL DEFAULT FALSE,
  join_approval BOOLEAN NOT NULL DEFAULT FALSE,
  status SMALLINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_groups_owner_id ON groups(owner_id);
CREATE INDEX idx_groups_status ON groups(status);

COMMENT ON TABLE groups IS '群聊表';
COMMENT ON COLUMN groups.id IS '群ID';
COMMENT ON COLUMN groups.name IS '群名称';
COMMENT ON COLUMN groups.avatar IS '群头像';
COMMENT ON COLUMN groups.description IS '群描述';
COMMENT ON COLUMN groups.announcement IS '群公告';
COMMENT ON COLUMN groups.owner_id IS '群主ID';
COMMENT ON COLUMN groups.member_count IS '成员数量';
COMMENT ON COLUMN groups.max_member_count IS '最大成员数';
COMMENT ON COLUMN groups.is_mute_all IS '是否全员禁言';
COMMENT ON COLUMN groups.join_approval IS '是否需要审批加入';
COMMENT ON COLUMN groups.status IS '状态: 0-解散, 1-正常';
COMMENT ON COLUMN groups.created_at IS '创建时间';
COMMENT ON COLUMN groups.updated_at IS '更新时间';

-- 群成员表
DROP TABLE IF EXISTS group_members CASCADE;
CREATE TABLE group_members (
  id BIGSERIAL PRIMARY KEY,
  group_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  role VARCHAR(20) NOT NULL DEFAULT 'member',
  nickname VARCHAR(50),
  is_muted BOOLEAN NOT NULL DEFAULT FALSE,
  muted_until TIMESTAMP,
  joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(group_id, user_id),
  CONSTRAINT check_group_members_role CHECK (role IN ('owner', 'admin', 'member'))
);

CREATE INDEX idx_group_members_group_id ON group_members(group_id);
CREATE INDEX idx_group_members_user_id ON group_members(user_id);
CREATE INDEX idx_group_members_role ON group_members(role);

COMMENT ON TABLE group_members IS '群成员表';
COMMENT ON COLUMN group_members.id IS '成员ID';
COMMENT ON COLUMN group_members.group_id IS '群ID';
COMMENT ON COLUMN group_members.user_id IS '用户ID';
COMMENT ON COLUMN group_members.role IS '角色: owner-群主, admin-管理员, member-普通成员';
COMMENT ON COLUMN group_members.nickname IS '群昵称';
COMMENT ON COLUMN group_members.is_muted IS '是否被禁言';
COMMENT ON COLUMN group_members.muted_until IS '禁言到期时间';
COMMENT ON COLUMN group_members.joined_at IS '加入时间';
COMMENT ON COLUMN group_members.updated_at IS '更新时间';

-- 私聊会话表
DROP TABLE IF EXISTS conversations CASCADE;
CREATE TABLE conversations (
  id BIGSERIAL PRIMARY KEY,
  user_id_1 BIGINT NOT NULL,
  user_id_2 BIGINT NOT NULL,
  last_message_id BIGINT,
  last_message_time TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(user_id_1, user_id_2)
);

CREATE INDEX idx_conversations_user_id_1 ON conversations(user_id_1);
CREATE INDEX idx_conversations_user_id_2 ON conversations(user_id_2);
CREATE INDEX idx_conversations_last_message_time ON conversations(last_message_time);

COMMENT ON TABLE conversations IS '私聊会话表';
COMMENT ON COLUMN conversations.id IS '会话ID';
COMMENT ON COLUMN conversations.user_id_1 IS '用户ID 1';
COMMENT ON COLUMN conversations.user_id_2 IS '用户ID 2';
COMMENT ON COLUMN conversations.last_message_id IS '最后一条消息ID';
COMMENT ON COLUMN conversations.last_message_time IS '最后消息时间';
COMMENT ON COLUMN conversations.created_at IS '创建时间';
COMMENT ON COLUMN conversations.updated_at IS '更新时间';

-- 私聊消息表
DROP TABLE IF EXISTS messages CASCADE;
CREATE TABLE messages (
  id BIGSERIAL PRIMARY KEY,
  conversation_id BIGINT NOT NULL,
  sender_id BIGINT NOT NULL,
  receiver_id BIGINT NOT NULL,
  message_type VARCHAR(20) NOT NULL DEFAULT 'text',
  content TEXT,
  media_url VARCHAR(500),
  is_read BOOLEAN NOT NULL DEFAULT FALSE,
  read_at TIMESTAMP,
  is_revoked BOOLEAN NOT NULL DEFAULT FALSE,
  revoked_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT check_messages_message_type CHECK (message_type IN ('text', 'image', 'voice', 'video', 'file', 'location', 'system'))
);

CREATE INDEX idx_messages_conversation_id ON messages(conversation_id);
CREATE INDEX idx_messages_sender_id ON messages(sender_id);
CREATE INDEX idx_messages_receiver_id ON messages(receiver_id);
CREATE INDEX idx_messages_created_at ON messages(created_at);

COMMENT ON TABLE messages IS '私聊消息表';
COMMENT ON COLUMN messages.id IS '消息ID';
COMMENT ON COLUMN messages.conversation_id IS '会话ID';
COMMENT ON COLUMN messages.sender_id IS '发送者ID';
COMMENT ON COLUMN messages.receiver_id IS '接收者ID';
COMMENT ON COLUMN messages.message_type IS '消息类型: text-文本, image-图片, voice-语音, video-视频, file-文件, location-位置, system-系统';
COMMENT ON COLUMN messages.content IS '消息内容';
COMMENT ON COLUMN messages.media_url IS '媒体文件URL';
COMMENT ON COLUMN messages.is_read IS '是否已读';
COMMENT ON COLUMN messages.read_at IS '阅读时间';
COMMENT ON COLUMN messages.is_revoked IS '是否撤回';
COMMENT ON COLUMN messages.revoked_at IS '撤回时间';
COMMENT ON COLUMN messages.created_at IS '创建时间';

-- 群聊消息表
DROP TABLE IF EXISTS group_messages CASCADE;
CREATE TABLE group_messages (
  id BIGSERIAL PRIMARY KEY,
  group_id BIGINT NOT NULL,
  sender_id BIGINT NOT NULL,
  message_type VARCHAR(20) NOT NULL DEFAULT 'text',
  content TEXT,
  media_url VARCHAR(500),
  is_revoked BOOLEAN NOT NULL DEFAULT FALSE,
  revoked_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT check_group_messages_message_type CHECK (message_type IN ('text', 'image', 'voice', 'video', 'file', 'location', 'system'))
);

CREATE INDEX idx_group_messages_group_id ON group_messages(group_id);
CREATE INDEX idx_group_messages_sender_id ON group_messages(sender_id);
CREATE INDEX idx_group_messages_created_at ON group_messages(created_at);

COMMENT ON TABLE group_messages IS '群聊消息表';
COMMENT ON COLUMN group_messages.id IS '消息ID';
COMMENT ON COLUMN group_messages.group_id IS '群ID';
COMMENT ON COLUMN group_messages.sender_id IS '发送者ID';
COMMENT ON COLUMN group_messages.message_type IS '消息类型: text-文本, image-图片, voice-语音, video-视频, file-文件, location-位置, system-系统';
COMMENT ON COLUMN group_messages.content IS '消息内容';
COMMENT ON COLUMN group_messages.media_url IS '媒体文件URL';
COMMENT ON COLUMN group_messages.is_revoked IS '是否撤回';
COMMENT ON COLUMN group_messages.revoked_at IS '撤回时间';
COMMENT ON COLUMN group_messages.created_at IS '创建时间';

-- 系统消息表
DROP TABLE IF EXISTS system_messages CASCADE;
CREATE TABLE system_messages (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  type VARCHAR(50) NOT NULL,
  title VARCHAR(200) NOT NULL,
  content TEXT NOT NULL,
  extra_data JSONB,
  is_read BOOLEAN NOT NULL DEFAULT FALSE,
  is_important BOOLEAN NOT NULL DEFAULT FALSE,
  clickable BOOLEAN NOT NULL DEFAULT FALSE,
  action_button JSONB,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_system_messages_user_id ON system_messages(user_id);
CREATE INDEX idx_system_messages_type ON system_messages(type);
CREATE INDEX idx_system_messages_is_read ON system_messages(is_read);
CREATE INDEX idx_system_messages_created_at ON system_messages(created_at);

COMMENT ON TABLE system_messages IS '系统消息表';
COMMENT ON COLUMN system_messages.id IS '消息ID';
COMMENT ON COLUMN system_messages.user_id IS '用户ID';
COMMENT ON COLUMN system_messages.type IS '消息类型';
COMMENT ON COLUMN system_messages.title IS '标题';
COMMENT ON COLUMN system_messages.content IS '内容';
COMMENT ON COLUMN system_messages.extra_data IS '额外数据';
COMMENT ON COLUMN system_messages.is_read IS '是否已读';
COMMENT ON COLUMN system_messages.is_important IS '是否重要';
COMMENT ON COLUMN system_messages.clickable IS '是否可点击';
COMMENT ON COLUMN system_messages.action_button IS '操作按钮配置';
COMMENT ON COLUMN system_messages.created_at IS '创建时间';

-- ============================================
-- 5. VIP会员系统表
-- ============================================

-- VIP套餐表
DROP TABLE IF EXISTS vip_plans CASCADE;
CREATE TABLE vip_plans (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  duration INTEGER NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  original_price DECIMAL(10,2),
  coin_price INTEGER,
  is_recommended BOOLEAN NOT NULL DEFAULT FALSE,
  benefits JSONB,
  transfer_fee_rate DECIMAL(5,4) NOT NULL DEFAULT 0.5000,
  sort_order INTEGER NOT NULL DEFAULT 0,
  status SMALLINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_vip_plans_status ON vip_plans(status);

COMMENT ON TABLE vip_plans IS 'VIP套餐表';
COMMENT ON COLUMN vip_plans.id IS '套餐ID';
COMMENT ON COLUMN vip_plans.name IS '套餐名称';
COMMENT ON COLUMN vip_plans.duration IS '时长（天）';
COMMENT ON COLUMN vip_plans.price IS '价格';
COMMENT ON COLUMN vip_plans.original_price IS '原价';
COMMENT ON COLUMN vip_plans.coin_price IS '爱心币价格';
COMMENT ON COLUMN vip_plans.is_recommended IS '是否推荐';
COMMENT ON COLUMN vip_plans.benefits IS '权益列表';
COMMENT ON COLUMN vip_plans.transfer_fee_rate IS '转账手续费率（0-1之间，如0.10表示10%）';
COMMENT ON COLUMN vip_plans.sort_order IS '排序';
COMMENT ON COLUMN vip_plans.status IS '状态: 0-下架, 1-上架';
COMMENT ON COLUMN vip_plans.created_at IS '创建时间';
COMMENT ON COLUMN vip_plans.updated_at IS '更新时间';

-- VIP订单表
DROP TABLE IF EXISTS vip_orders CASCADE;
CREATE TABLE vip_orders (
  id BIGSERIAL PRIMARY KEY,
  order_no VARCHAR(50) NOT NULL UNIQUE,
  user_id BIGINT NOT NULL,
  plan_id BIGINT NOT NULL,
  duration INTEGER NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  coin_amount INTEGER,
  payment_method VARCHAR(50),
  payment_time TIMESTAMP,
  status SMALLINT NOT NULL DEFAULT 0,
  start_time TIMESTAMP,
  expire_time TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_vip_orders_user_id ON vip_orders(user_id);
CREATE INDEX idx_vip_orders_plan_id ON vip_orders(plan_id);
CREATE INDEX idx_vip_orders_status ON vip_orders(status);

COMMENT ON TABLE vip_orders IS 'VIP订单表';
COMMENT ON COLUMN vip_orders.id IS '订单ID';
COMMENT ON COLUMN vip_orders.order_no IS '订单号';
COMMENT ON COLUMN vip_orders.user_id IS '用户ID';
COMMENT ON COLUMN vip_orders.plan_id IS '套餐ID';
COMMENT ON COLUMN vip_orders.duration IS '时长（天）';
COMMENT ON COLUMN vip_orders.amount IS '金额';
COMMENT ON COLUMN vip_orders.coin_amount IS '爱心币支付数量';
COMMENT ON COLUMN vip_orders.payment_method IS '支付方式';
COMMENT ON COLUMN vip_orders.payment_time IS '支付时间';
COMMENT ON COLUMN vip_orders.status IS '状态: 0-待支付, 1-已支付, 2-已取消, 3-已退款';
COMMENT ON COLUMN vip_orders.start_time IS '生效时间';
COMMENT ON COLUMN vip_orders.expire_time IS '过期时间';
COMMENT ON COLUMN vip_orders.created_at IS '创建时间';
COMMENT ON COLUMN vip_orders.updated_at IS '更新时间';

-- ============================================
-- 6. 货币与交易系统表
-- ============================================

-- 用户货币账户表
DROP TABLE IF EXISTS user_coin_accounts CASCADE;
CREATE TABLE user_coin_accounts (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL UNIQUE,
  love_coin INTEGER NOT NULL DEFAULT 0,
  total_earned INTEGER NOT NULL DEFAULT 0,
  total_spent INTEGER NOT NULL DEFAULT 0,
  level VARCHAR(20) NOT NULL DEFAULT 'Bronze',
  level_progress INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_user_coin_accounts_user_id ON user_coin_accounts(user_id);

COMMENT ON TABLE user_coin_accounts IS '用户货币账户表';
COMMENT ON COLUMN user_coin_accounts.id IS '账户ID';
COMMENT ON COLUMN user_coin_accounts.user_id IS '用户ID';
COMMENT ON COLUMN user_coin_accounts.love_coin IS '爱心币余额';
COMMENT ON COLUMN user_coin_accounts.total_earned IS '总获得';
COMMENT ON COLUMN user_coin_accounts.total_spent IS '总消费';
COMMENT ON COLUMN user_coin_accounts.level IS '等级';
COMMENT ON COLUMN user_coin_accounts.level_progress IS '等级进度';
COMMENT ON COLUMN user_coin_accounts.created_at IS '创建时间';
COMMENT ON COLUMN user_coin_accounts.updated_at IS '更新时间';

-- 货币交易记录表
DROP TABLE IF EXISTS coin_transactions CASCADE;
CREATE TABLE coin_transactions (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  counterparty_user_id BIGINT,
  type VARCHAR(20) NOT NULL,
  amount INTEGER NOT NULL,
  fee_amount INTEGER NOT NULL DEFAULT 0,
  balance_before INTEGER NOT NULL,
  balance_after INTEGER NOT NULL,
  source VARCHAR(50) NOT NULL,
  title VARCHAR(200) NOT NULL,
  description VARCHAR(500),
  remark VARCHAR(200),
  related_id BIGINT,
  related_type VARCHAR(50),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT check_coin_transactions_type CHECK (type IN ('earn', 'spend', 'transfer_in', 'transfer_out'))
);

CREATE INDEX idx_coin_transactions_user_id ON coin_transactions(user_id);
CREATE INDEX idx_coin_transactions_counterparty_user_id ON coin_transactions(counterparty_user_id);
CREATE INDEX idx_coin_transactions_type ON coin_transactions(type);
CREATE INDEX idx_coin_transactions_source ON coin_transactions(source);
CREATE INDEX idx_coin_transactions_created_at ON coin_transactions(created_at);

COMMENT ON TABLE coin_transactions IS '货币交易记录表';
COMMENT ON COLUMN coin_transactions.id IS '交易ID';
COMMENT ON COLUMN coin_transactions.user_id IS '用户ID';
COMMENT ON COLUMN coin_transactions.counterparty_user_id IS '交易对方用户ID（转账时使用）';
COMMENT ON COLUMN coin_transactions.type IS '交易类型: earn-获得, spend-消费, transfer_in-转入, transfer_out-转出';
COMMENT ON COLUMN coin_transactions.amount IS '金额（正数为获得，负数为消费）';
COMMENT ON COLUMN coin_transactions.fee_amount IS '手续费金额';
COMMENT ON COLUMN coin_transactions.balance_before IS '交易前余额';
COMMENT ON COLUMN coin_transactions.balance_after IS '交易后余额';
COMMENT ON COLUMN coin_transactions.source IS '来源';
COMMENT ON COLUMN coin_transactions.title IS '标题';
COMMENT ON COLUMN coin_transactions.description IS '描述';
COMMENT ON COLUMN coin_transactions.remark IS '备注（转账留言）';
COMMENT ON COLUMN coin_transactions.related_id IS '关联ID';
COMMENT ON COLUMN coin_transactions.related_type IS '关联类型';
COMMENT ON COLUMN coin_transactions.created_at IS '创建时间';

-- ============================================
-- 7. 任务系统表
-- ============================================

-- 任务配置表
DROP TABLE IF EXISTS task_configs CASCADE;
CREATE TABLE task_configs (
  id BIGSERIAL PRIMARY KEY,
  task_key VARCHAR(50) NOT NULL UNIQUE,
  title VARCHAR(100) NOT NULL,
  description VARCHAR(500),
  type VARCHAR(20) NOT NULL,
  target INTEGER NOT NULL,
  reward INTEGER NOT NULL,
  icon VARCHAR(50),
  sort_order INTEGER NOT NULL DEFAULT 0,
  status SMALLINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT check_task_configs_type CHECK (type IN ('daily', 'weekly', 'achievement', 'newbie'))
);

CREATE INDEX idx_task_configs_type ON task_configs(type);
CREATE INDEX idx_task_configs_status ON task_configs(status);

COMMENT ON TABLE task_configs IS '任务配置表';
COMMENT ON COLUMN task_configs.id IS '任务ID';
COMMENT ON COLUMN task_configs.task_key IS '任务标识';
COMMENT ON COLUMN task_configs.title IS '任务标题';
COMMENT ON COLUMN task_configs.description IS '任务描述';
COMMENT ON COLUMN task_configs.type IS '任务类型: daily-每日, weekly-每周, achievement-成就, newbie-新手';
COMMENT ON COLUMN task_configs.target IS '目标值';
COMMENT ON COLUMN task_configs.reward IS '奖励金额';
COMMENT ON COLUMN task_configs.icon IS '图标';
COMMENT ON COLUMN task_configs.sort_order IS '排序';
COMMENT ON COLUMN task_configs.status IS '状态: 0-禁用, 1-启用';
COMMENT ON COLUMN task_configs.created_at IS '创建时间';
COMMENT ON COLUMN task_configs.updated_at IS '更新时间';

-- 用户任务表
DROP TABLE IF EXISTS user_tasks CASCADE;
CREATE TABLE user_tasks (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  task_id BIGINT NOT NULL,
  progress INTEGER NOT NULL DEFAULT 0,
  target INTEGER NOT NULL,
  is_completed BOOLEAN NOT NULL DEFAULT FALSE,
  completed_at TIMESTAMP,
  is_claimed BOOLEAN NOT NULL DEFAULT FALSE,
  claimed_at TIMESTAMP,
  date DATE NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(user_id, task_id, date)
);

CREATE INDEX idx_user_tasks_user_id ON user_tasks(user_id);
CREATE INDEX idx_user_tasks_task_id ON user_tasks(task_id);
CREATE INDEX idx_user_tasks_date ON user_tasks(date);

COMMENT ON TABLE user_tasks IS '用户任务表';
COMMENT ON COLUMN user_tasks.id IS '用户任务ID';
COMMENT ON COLUMN user_tasks.user_id IS '用户ID';
COMMENT ON COLUMN user_tasks.task_id IS '任务ID';
COMMENT ON COLUMN user_tasks.progress IS '当前进度';
COMMENT ON COLUMN user_tasks.target IS '目标值';
COMMENT ON COLUMN user_tasks.is_completed IS '是否完成';
COMMENT ON COLUMN user_tasks.completed_at IS '完成时间';
COMMENT ON COLUMN user_tasks.is_claimed IS '是否领取';
COMMENT ON COLUMN user_tasks.claimed_at IS '领取时间';
COMMENT ON COLUMN user_tasks.date IS '日期（用于每日任务重置）';
COMMENT ON COLUMN user_tasks.created_at IS '创建时间';
COMMENT ON COLUMN user_tasks.updated_at IS '更新时间';

-- 签到记录表
DROP TABLE IF EXISTS checkin_records CASCADE;
CREATE TABLE checkin_records (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  date DATE NOT NULL,
  consecutive_days INTEGER NOT NULL DEFAULT 1,
  reward INTEGER NOT NULL,
  bonus_reward INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(user_id, date)
);

CREATE INDEX idx_checkin_records_user_id ON checkin_records(user_id);
CREATE INDEX idx_checkin_records_date ON checkin_records(date);

COMMENT ON TABLE checkin_records IS '签到记录表';
COMMENT ON COLUMN checkin_records.id IS '签到ID';
COMMENT ON COLUMN checkin_records.user_id IS '用户ID';
COMMENT ON COLUMN checkin_records.date IS '签到日期';
COMMENT ON COLUMN checkin_records.consecutive_days IS '连续签到天数';
COMMENT ON COLUMN checkin_records.reward IS '奖励金额';
COMMENT ON COLUMN checkin_records.bonus_reward IS '额外奖励';
COMMENT ON COLUMN checkin_records.created_at IS '创建时间';

-- ============================================
-- 8. 商城系统表
-- ============================================

-- 商品表
DROP TABLE IF EXISTS shop_items CASCADE;
CREATE TABLE shop_items (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(500),
  category VARCHAR(50) NOT NULL,
  type VARCHAR(50) NOT NULL,
  price INTEGER NOT NULL,
  original_price INTEGER,
  icon VARCHAR(50),
  image VARCHAR(500),
  benefits JSONB,
  duration INTEGER,
  stock INTEGER NOT NULL DEFAULT -1,
  sales_count INTEGER NOT NULL DEFAULT 0,
  is_hot BOOLEAN NOT NULL DEFAULT FALSE,
  sort_order INTEGER NOT NULL DEFAULT 0,
  status SMALLINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_shop_items_category ON shop_items(category);
CREATE INDEX idx_shop_items_type ON shop_items(type);
CREATE INDEX idx_shop_items_is_hot ON shop_items(is_hot);
CREATE INDEX idx_shop_items_status ON shop_items(status);

COMMENT ON TABLE shop_items IS '商品表';
COMMENT ON COLUMN shop_items.id IS '商品ID';
COMMENT ON COLUMN shop_items.name IS '商品名称';
COMMENT ON COLUMN shop_items.description IS '商品描述';
COMMENT ON COLUMN shop_items.category IS '分类';
COMMENT ON COLUMN shop_items.type IS '类型';
COMMENT ON COLUMN shop_items.price IS '价格（爱心币）';
COMMENT ON COLUMN shop_items.original_price IS '原价';
COMMENT ON COLUMN shop_items.icon IS '图标';
COMMENT ON COLUMN shop_items.image IS '图片';
COMMENT ON COLUMN shop_items.benefits IS '权益列表';
COMMENT ON COLUMN shop_items.duration IS '有效期（天）';
COMMENT ON COLUMN shop_items.stock IS '库存（-1表示无限）';
COMMENT ON COLUMN shop_items.sales_count IS '销量';
COMMENT ON COLUMN shop_items.is_hot IS '是否热门';
COMMENT ON COLUMN shop_items.sort_order IS '排序';
COMMENT ON COLUMN shop_items.status IS '状态: 0-下架, 1-上架';
COMMENT ON COLUMN shop_items.created_at IS '创建时间';
COMMENT ON COLUMN shop_items.updated_at IS '更新时间';

-- 商城订单表
DROP TABLE IF EXISTS shop_orders CASCADE;
CREATE TABLE shop_orders (
  id BIGSERIAL PRIMARY KEY,
  order_no VARCHAR(50) NOT NULL UNIQUE,
  user_id BIGINT NOT NULL,
  item_id BIGINT NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 1,
  total_price INTEGER NOT NULL,
  status SMALLINT NOT NULL DEFAULT 0,
  paid_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_shop_orders_user_id ON shop_orders(user_id);
CREATE INDEX idx_shop_orders_item_id ON shop_orders(item_id);
CREATE INDEX idx_shop_orders_status ON shop_orders(status);

COMMENT ON TABLE shop_orders IS '商城订单表';
COMMENT ON COLUMN shop_orders.id IS '订单ID';
COMMENT ON COLUMN shop_orders.order_no IS '订单号';
COMMENT ON COLUMN shop_orders.user_id IS '用户ID';
COMMENT ON COLUMN shop_orders.item_id IS '商品ID';
COMMENT ON COLUMN shop_orders.quantity IS '数量';
COMMENT ON COLUMN shop_orders.total_price IS '总价';
COMMENT ON COLUMN shop_orders.status IS '状态: 0-待支付, 1-已支付, 2-已取消';
COMMENT ON COLUMN shop_orders.paid_at IS '支付时间';
COMMENT ON COLUMN shop_orders.created_at IS '创建时间';
COMMENT ON COLUMN shop_orders.updated_at IS '更新时间';

-- 用户道具表
DROP TABLE IF EXISTS user_items CASCADE;
CREATE TABLE user_items (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  item_id BIGINT NOT NULL,
  order_id BIGINT,
  quantity INTEGER NOT NULL DEFAULT 1,
  status VARCHAR(20) NOT NULL DEFAULT 'unused',
  obtained_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expire_at TIMESTAMP,
  used_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT check_user_items_status CHECK (status IN ('unused', 'active', 'expired', 'used'))
);

CREATE INDEX idx_user_items_user_id ON user_items(user_id);
CREATE INDEX idx_user_items_item_id ON user_items(item_id);
CREATE INDEX idx_user_items_status ON user_items(status);

COMMENT ON TABLE user_items IS '用户道具表';
COMMENT ON COLUMN user_items.id IS '道具ID';
COMMENT ON COLUMN user_items.user_id IS '用户ID';
COMMENT ON COLUMN user_items.item_id IS '商品ID';
COMMENT ON COLUMN user_items.order_id IS '订单ID';
COMMENT ON COLUMN user_items.quantity IS '数量';
COMMENT ON COLUMN user_items.status IS '道具状态: unused-未使用, active-激活中, expired-已过期, used-已使用';
COMMENT ON COLUMN user_items.obtained_at IS '获得时间';
COMMENT ON COLUMN user_items.expire_at IS '过期时间';
COMMENT ON COLUMN user_items.used_at IS '使用时间';
COMMENT ON COLUMN user_items.created_at IS '创建时间';
COMMENT ON COLUMN user_items.updated_at IS '更新时间';

-- ============================================
-- 9. 邀请系统表
-- ============================================

-- 邀请码表
DROP TABLE IF EXISTS invite_codes CASCADE;
CREATE TABLE invite_codes (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL UNIQUE,
  code VARCHAR(20) NOT NULL UNIQUE,
  total_invites INTEGER NOT NULL DEFAULT 0,
  valid_invites INTEGER NOT NULL DEFAULT 0,
  total_rewards INTEGER NOT NULL DEFAULT 0,
  expire_at TIMESTAMP,
  status SMALLINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_invite_codes_status ON invite_codes(status);

COMMENT ON TABLE invite_codes IS '邀请码表';
COMMENT ON COLUMN invite_codes.id IS '邀请码ID';
COMMENT ON COLUMN invite_codes.user_id IS '用户ID';
COMMENT ON COLUMN invite_codes.code IS '邀请码';
COMMENT ON COLUMN invite_codes.total_invites IS '总邀请人数';
COMMENT ON COLUMN invite_codes.valid_invites IS '有效邀请人数';
COMMENT ON COLUMN invite_codes.total_rewards IS '总获得奖励';
COMMENT ON COLUMN invite_codes.expire_at IS '过期时间';
COMMENT ON COLUMN invite_codes.status IS '状态: 0-失效, 1-有效';
COMMENT ON COLUMN invite_codes.created_at IS '创建时间';
COMMENT ON COLUMN invite_codes.updated_at IS '更新时间';

-- 邀请记录表
DROP TABLE IF EXISTS invite_records CASCADE;
CREATE TABLE invite_records (
  id BIGSERIAL PRIMARY KEY,
  inviter_id BIGINT NOT NULL,
  invitee_id BIGINT NOT NULL UNIQUE,
  invite_code VARCHAR(20) NOT NULL,
  inviter_reward INTEGER NOT NULL DEFAULT 0,
  invitee_reward INTEGER NOT NULL DEFAULT 0,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  registered_at TIMESTAMP,
  completed_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT check_invite_records_status CHECK (status IN ('pending', 'completed', 'expired', 'rewarded'))
);

CREATE INDEX idx_invite_records_inviter_id ON invite_records(inviter_id);
CREATE INDEX idx_invite_records_invite_code ON invite_records(invite_code);
CREATE INDEX idx_invite_records_status ON invite_records(status);

COMMENT ON TABLE invite_records IS '邀请记录表';
COMMENT ON COLUMN invite_records.id IS '记录ID';
COMMENT ON COLUMN invite_records.inviter_id IS '邀请人ID';
COMMENT ON COLUMN invite_records.invitee_id IS '被邀请人ID';
COMMENT ON COLUMN invite_records.invite_code IS '邀请码';
COMMENT ON COLUMN invite_records.inviter_reward IS '邀请人奖励';
COMMENT ON COLUMN invite_records.invitee_reward IS '被邀请人奖励';
COMMENT ON COLUMN invite_records.status IS '邀请状态: pending-待完成, completed-已完成, expired-已过期, rewarded-奖励已领取';
COMMENT ON COLUMN invite_records.registered_at IS '注册时间';
COMMENT ON COLUMN invite_records.completed_at IS '完成时间';
COMMENT ON COLUMN invite_records.created_at IS '创建时间';
COMMENT ON COLUMN invite_records.updated_at IS '更新时间';

-- ============================================
-- 10. 流动性挖矿系统表
-- ============================================

-- 挖矿池表
DROP TABLE IF EXISTS mining_pools CASCADE;
CREATE TABLE mining_pools (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  pool_type VARCHAR(50) NOT NULL,
  description TEXT,
  staking_token VARCHAR(50) NOT NULL,
  reward_tokens JSONB NOT NULL,
  min_stake INTEGER NOT NULL,
  lock_period INTEGER NOT NULL,
  current_apy DECIMAL(10,2) NOT NULL,
  total_liquidity BIGINT NOT NULL DEFAULT 0,
  total_stakers INTEGER NOT NULL DEFAULT 0,
  risk_level VARCHAR(20) NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_mining_pools_pool_type ON mining_pools(pool_type);
CREATE INDEX idx_mining_pools_is_active ON mining_pools(is_active);

COMMENT ON TABLE mining_pools IS '挖矿池表';
COMMENT ON COLUMN mining_pools.id IS '矿池ID';
COMMENT ON COLUMN mining_pools.name IS '矿池名称';
COMMENT ON COLUMN mining_pools.pool_type IS '矿池类型';
COMMENT ON COLUMN mining_pools.description IS '描述';
COMMENT ON COLUMN mining_pools.staking_token IS '质押代币';
COMMENT ON COLUMN mining_pools.reward_tokens IS '奖励代币数组';
COMMENT ON COLUMN mining_pools.min_stake IS '最小质押量';
COMMENT ON COLUMN mining_pools.lock_period IS '锁定期（天）';
COMMENT ON COLUMN mining_pools.current_apy IS '当前年化收益率';
COMMENT ON COLUMN mining_pools.total_liquidity IS '总流动性';
COMMENT ON COLUMN mining_pools.total_stakers IS '质押人数';
COMMENT ON COLUMN mining_pools.risk_level IS '风险等级';
COMMENT ON COLUMN mining_pools.is_active IS '是否活跃';
COMMENT ON COLUMN mining_pools.sort_order IS '排序';
COMMENT ON COLUMN mining_pools.created_at IS '创建时间';
COMMENT ON COLUMN mining_pools.updated_at IS '更新时间';

-- 质押记录表
DROP TABLE IF EXISTS staking_records CASCADE;
CREATE TABLE staking_records (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  pool_id BIGINT NOT NULL,
  amount INTEGER NOT NULL,
  current_rewards DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  total_claimed DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  status VARCHAR(20) NOT NULL DEFAULT 'staking',
  staking_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expected_unlock_time TIMESTAMP NOT NULL,
  unstaking_time TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT check_staking_records_status CHECK (status IN ('staking', 'unstaking', 'completed'))
);

CREATE INDEX idx_staking_records_user_id ON staking_records(user_id);
CREATE INDEX idx_staking_records_pool_id ON staking_records(pool_id);
CREATE INDEX idx_staking_records_status ON staking_records(status);

COMMENT ON TABLE staking_records IS '质押记录表';
COMMENT ON COLUMN staking_records.id IS '质押ID';
COMMENT ON COLUMN staking_records.user_id IS '用户ID';
COMMENT ON COLUMN staking_records.pool_id IS '矿池ID';
COMMENT ON COLUMN staking_records.amount IS '质押数量';
COMMENT ON COLUMN staking_records.current_rewards IS '当前奖励';
COMMENT ON COLUMN staking_records.total_claimed IS '已领取奖励';
COMMENT ON COLUMN staking_records.status IS '质押状态: staking-质押中, unstaking-解押中, completed-已完成';
COMMENT ON COLUMN staking_records.staking_time IS '质押时间';
COMMENT ON COLUMN staking_records.expected_unlock_time IS '预期解锁时间';
COMMENT ON COLUMN staking_records.unstaking_time IS '提取时间';
COMMENT ON COLUMN staking_records.created_at IS '创建时间';
COMMENT ON COLUMN staking_records.updated_at IS '更新时间';

-- 挖矿奖励领取记录表
DROP TABLE IF EXISTS mining_claim_records CASCADE;
CREATE TABLE mining_claim_records (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  staking_id BIGINT NOT NULL,
  pool_id BIGINT NOT NULL,
  reward_amount DECIMAL(10,2) NOT NULL,
  bonus_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  transaction_id VARCHAR(100) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_mining_claim_records_user_id ON mining_claim_records(user_id);
CREATE INDEX idx_mining_claim_records_staking_id ON mining_claim_records(staking_id);
CREATE INDEX idx_mining_claim_records_pool_id ON mining_claim_records(pool_id);

COMMENT ON TABLE mining_claim_records IS '挖矿奖励领取记录表';
COMMENT ON COLUMN mining_claim_records.id IS '领取ID';
COMMENT ON COLUMN mining_claim_records.user_id IS '用户ID';
COMMENT ON COLUMN mining_claim_records.staking_id IS '质押ID';
COMMENT ON COLUMN mining_claim_records.pool_id IS '矿池ID';
COMMENT ON COLUMN mining_claim_records.reward_amount IS '奖励数量';
COMMENT ON COLUMN mining_claim_records.bonus_amount IS '额外奖励';
COMMENT ON COLUMN mining_claim_records.transaction_id IS '交易ID';
COMMENT ON COLUMN mining_claim_records.created_at IS '创建时间';

-- ============================================
-- 11. 广告与活动系统表
-- ============================================

-- 广告观看记录表
DROP TABLE IF EXISTS ad_watch_records CASCADE;
CREATE TABLE ad_watch_records (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  ad_type VARCHAR(50) NOT NULL,
  duration INTEGER NOT NULL,
  reward INTEGER NOT NULL,
  date DATE NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_ad_watch_records_user_id ON ad_watch_records(user_id);
CREATE INDEX idx_ad_watch_records_date ON ad_watch_records(date);

COMMENT ON TABLE ad_watch_records IS '广告观看记录表';
COMMENT ON COLUMN ad_watch_records.id IS '记录ID';
COMMENT ON COLUMN ad_watch_records.user_id IS '用户ID';
COMMENT ON COLUMN ad_watch_records.ad_type IS '广告类型';
COMMENT ON COLUMN ad_watch_records.duration IS '时长（秒）';
COMMENT ON COLUMN ad_watch_records.reward IS '奖励';
COMMENT ON COLUMN ad_watch_records.date IS '日期';
COMMENT ON COLUMN ad_watch_records.created_at IS '创建时间';

-- 活动表
DROP TABLE IF EXISTS activities CASCADE;
CREATE TABLE activities (
  id BIGSERIAL PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  description TEXT,
  type VARCHAR(50) NOT NULL,
  banner_image VARCHAR(500),
  rules JSONB,
  rewards JSONB,
  start_time TIMESTAMP NOT NULL,
  end_time TIMESTAMP NOT NULL,
  status SMALLINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_activities_type ON activities(type);
CREATE INDEX idx_activities_start_end_time ON activities(start_time, end_time);
CREATE INDEX idx_activities_status ON activities(status);

COMMENT ON TABLE activities IS '活动表';
COMMENT ON COLUMN activities.id IS '活动ID';
COMMENT ON COLUMN activities.title IS '活动标题';
COMMENT ON COLUMN activities.description IS '活动描述';
COMMENT ON COLUMN activities.type IS '活动类型';
COMMENT ON COLUMN activities.banner_image IS '横幅图';
COMMENT ON COLUMN activities.rules IS '规则配置';
COMMENT ON COLUMN activities.rewards IS '奖励配置';
COMMENT ON COLUMN activities.start_time IS '开始时间';
COMMENT ON COLUMN activities.end_time IS '结束时间';
COMMENT ON COLUMN activities.status IS '状态: 0-下架, 1-上架';
COMMENT ON COLUMN activities.created_at IS '创建时间';
COMMENT ON COLUMN activities.updated_at IS '更新时间';

-- 用户活动参与记录表
DROP TABLE IF EXISTS user_activity_records CASCADE;
CREATE TABLE user_activity_records (
  id BIGSERIAL PRIMARY KEY,
  activity_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  progress JSONB,
  rewards_claimed JSONB,
  status SMALLINT NOT NULL DEFAULT 1,
  joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(activity_id, user_id)
);

CREATE INDEX idx_user_activity_records_activity_id ON user_activity_records(activity_id);
CREATE INDEX idx_user_activity_records_user_id ON user_activity_records(user_id);

COMMENT ON TABLE user_activity_records IS '用户活动参与记录表';
COMMENT ON COLUMN user_activity_records.id IS '记录ID';
COMMENT ON COLUMN user_activity_records.activity_id IS '活动ID';
COMMENT ON COLUMN user_activity_records.user_id IS '用户ID';
COMMENT ON COLUMN user_activity_records.progress IS '进度数据';
COMMENT ON COLUMN user_activity_records.rewards_claimed IS '已领取奖励';
COMMENT ON COLUMN user_activity_records.status IS '状态: 0-未参与, 1-进行中, 2-已完成';
COMMENT ON COLUMN user_activity_records.joined_at IS '参与时间';
COMMENT ON COLUMN user_activity_records.completed_at IS '完成时间';
COMMENT ON COLUMN user_activity_records.created_at IS '创建时间';
COMMENT ON COLUMN user_activity_records.updated_at IS '更新时间';

-- ============================================
-- 12. 地区数据表
-- ============================================

-- 地区表
DROP TABLE IF EXISTS regions CASCADE;
CREATE TABLE regions (
  id SERIAL PRIMARY KEY,
  parent_id INTEGER,
  name VARCHAR(100) NOT NULL,
  level SMALLINT NOT NULL,
  pinyin VARCHAR(100),
  code VARCHAR(20),
  sort_order INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX idx_regions_parent_id ON regions(parent_id);
CREATE INDEX idx_regions_level ON regions(level);
CREATE INDEX idx_regions_code ON regions(code);

COMMENT ON TABLE regions IS '地区表';
COMMENT ON COLUMN regions.id IS '地区ID';
COMMENT ON COLUMN regions.parent_id IS '父级ID';
COMMENT ON COLUMN regions.name IS '地区名称';
COMMENT ON COLUMN regions.level IS '级别: 1-省, 2-市, 3-区';
COMMENT ON COLUMN regions.pinyin IS '拼音';
COMMENT ON COLUMN regions.code IS '地区编码';
COMMENT ON COLUMN regions.sort_order IS '排序';

-- ============================================
-- 13. 认证与安全表
-- ============================================

-- 验证码表
DROP TABLE IF EXISTS verification_codes CASCADE;
CREATE TABLE verification_codes (
  id BIGSERIAL PRIMARY KEY,
  phone VARCHAR(20) NOT NULL,
  code VARCHAR(10) NOT NULL,
  type VARCHAR(20) NOT NULL,
  status SMALLINT NOT NULL DEFAULT 0,
  expire_at TIMESTAMP NOT NULL,
  used_at TIMESTAMP,
  ip_address VARCHAR(50),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT check_verification_codes_type CHECK (type IN ('login', 'register', 'reset_password', 'change_phone', 'bind_phone'))
);

CREATE INDEX idx_verification_codes_phone ON verification_codes(phone);
CREATE INDEX idx_verification_codes_code ON verification_codes(code);
CREATE INDEX idx_verification_codes_expire_at ON verification_codes(expire_at);
CREATE INDEX idx_verification_codes_status ON verification_codes(status);
CREATE INDEX idx_verification_codes_phone_type_status ON verification_codes(phone, type, status);

COMMENT ON TABLE verification_codes IS '验证码表';
COMMENT ON COLUMN verification_codes.id IS '验证码ID';
COMMENT ON COLUMN verification_codes.phone IS '手机号';
COMMENT ON COLUMN verification_codes.code IS '验证码';
COMMENT ON COLUMN verification_codes.type IS '验证码类型: login-登录, register-注册, reset_password-重置密码, change_phone-更换手机, bind_phone-绑定手机';
COMMENT ON COLUMN verification_codes.status IS '状态: 0-未使用, 1-已使用, 2-已过期';
COMMENT ON COLUMN verification_codes.expire_at IS '过期时间';
COMMENT ON COLUMN verification_codes.used_at IS '使用时间';
COMMENT ON COLUMN verification_codes.ip_address IS 'IP地址';
COMMENT ON COLUMN verification_codes.created_at IS '创建时间';

-- ============================================
-- 14. 系统配置与日志表
-- ============================================

-- 系统配置表
DROP TABLE IF EXISTS system_configs CASCADE;
CREATE TABLE system_configs (
  id SERIAL PRIMARY KEY,
  config_key VARCHAR(100) NOT NULL UNIQUE,
  config_value TEXT,
  description VARCHAR(500),
  group_name VARCHAR(50),
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_system_configs_group_name ON system_configs(group_name);

COMMENT ON TABLE system_configs IS '系统配置表';
COMMENT ON COLUMN system_configs.id IS '配置ID';
COMMENT ON COLUMN system_configs.config_key IS '配置键';
COMMENT ON COLUMN system_configs.config_value IS '配置值';
COMMENT ON COLUMN system_configs.description IS '描述';
COMMENT ON COLUMN system_configs.group_name IS '分组';
COMMENT ON COLUMN system_configs.updated_at IS '更新时间';

-- 用户行为日志表
DROP TABLE IF EXISTS user_action_logs CASCADE;
CREATE TABLE user_action_logs (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  action VARCHAR(50) NOT NULL,
  module VARCHAR(50) NOT NULL,
  target_type VARCHAR(50),
  target_id BIGINT,
  ip_address VARCHAR(50),
  user_agent VARCHAR(500),
  extra_data JSONB,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_user_action_logs_user_id ON user_action_logs(user_id);
CREATE INDEX idx_user_action_logs_action ON user_action_logs(action);
CREATE INDEX idx_user_action_logs_module ON user_action_logs(module);
CREATE INDEX idx_user_action_logs_created_at ON user_action_logs(created_at);

COMMENT ON TABLE user_action_logs IS '用户行为日志表';
COMMENT ON COLUMN user_action_logs.id IS '日志ID';
COMMENT ON COLUMN user_action_logs.user_id IS '用户ID';
COMMENT ON COLUMN user_action_logs.action IS '操作';
COMMENT ON COLUMN user_action_logs.module IS '模块';
COMMENT ON COLUMN user_action_logs.target_type IS '目标类型';
COMMENT ON COLUMN user_action_logs.target_id IS '目标ID';
COMMENT ON COLUMN user_action_logs.ip_address IS 'IP地址';
COMMENT ON COLUMN user_action_logs.user_agent IS 'User Agent';
COMMENT ON COLUMN user_action_logs.extra_data IS '额外数据';
COMMENT ON COLUMN user_action_logs.created_at IS '创建时间';

-- 管理员操作日志表
DROP TABLE IF EXISTS admin_logs CASCADE;
CREATE TABLE admin_logs (
  id BIGSERIAL PRIMARY KEY,
  admin_id BIGINT NOT NULL,
  action VARCHAR(100) NOT NULL,
  module VARCHAR(50) NOT NULL,
  target_type VARCHAR(50),
  target_id BIGINT,
  description TEXT,
  ip_address VARCHAR(50),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_admin_logs_admin_id ON admin_logs(admin_id);
CREATE INDEX idx_admin_logs_action ON admin_logs(action);
CREATE INDEX idx_admin_logs_module ON admin_logs(module);
CREATE INDEX idx_admin_logs_created_at ON admin_logs(created_at);

COMMENT ON TABLE admin_logs IS '管理员操作日志表';
COMMENT ON COLUMN admin_logs.id IS '日志ID';
COMMENT ON COLUMN admin_logs.admin_id IS '管理员ID';
COMMENT ON COLUMN admin_logs.action IS '操作';
COMMENT ON COLUMN admin_logs.module IS '模块';
COMMENT ON COLUMN admin_logs.target_type IS '目标类型';
COMMENT ON COLUMN admin_logs.target_id IS '目标ID';
COMMENT ON COLUMN admin_logs.description IS '描述';
COMMENT ON COLUMN admin_logs.ip_address IS 'IP地址';
COMMENT ON COLUMN admin_logs.created_at IS '创建时间';

-- ============================================
-- 15. 统计表
-- ============================================

-- 每日用户操作统计表
DROP TABLE IF EXISTS daily_user_stats CASCADE;
CREATE TABLE daily_user_stats (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  date DATE NOT NULL,
  like_count INTEGER NOT NULL DEFAULT 0,
  super_like_count INTEGER NOT NULL DEFAULT 0,
  follow_count INTEGER NOT NULL DEFAULT 0,
  post_count INTEGER NOT NULL DEFAULT 0,
  comment_count INTEGER NOT NULL DEFAULT 0,
  new_matches INTEGER NOT NULL DEFAULT 0,
  new_followers INTEGER NOT NULL DEFAULT 0,
  ad_watch_count INTEGER NOT NULL DEFAULT 0,
  login_count INTEGER NOT NULL DEFAULT 0,
  active_minutes INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(user_id, date)
);

CREATE INDEX idx_daily_user_stats_user_id ON daily_user_stats(user_id);
CREATE INDEX idx_daily_user_stats_date ON daily_user_stats(date);

COMMENT ON TABLE daily_user_stats IS '每日用户操作统计表';
COMMENT ON COLUMN daily_user_stats.id IS '统计ID';
COMMENT ON COLUMN daily_user_stats.user_id IS '用户ID';
COMMENT ON COLUMN daily_user_stats.date IS '日期';
COMMENT ON COLUMN daily_user_stats.like_count IS '喜欢次数';
COMMENT ON COLUMN daily_user_stats.super_like_count IS '超级喜欢次数';
COMMENT ON COLUMN daily_user_stats.follow_count IS '关注次数';
COMMENT ON COLUMN daily_user_stats.post_count IS '发帖次数';
COMMENT ON COLUMN daily_user_stats.comment_count IS '评论次数';
COMMENT ON COLUMN daily_user_stats.new_matches IS '新配对数';
COMMENT ON COLUMN daily_user_stats.new_followers IS '新粉丝数';
COMMENT ON COLUMN daily_user_stats.ad_watch_count IS '观看广告次数';
COMMENT ON COLUMN daily_user_stats.login_count IS '登录次数';
COMMENT ON COLUMN daily_user_stats.active_minutes IS '活跃时长（分钟）';
COMMENT ON COLUMN daily_user_stats.created_at IS '创建时间';
COMMENT ON COLUMN daily_user_stats.updated_at IS '更新时间';

-- ============================================
-- 创建更新时间触发器函数
-- ============================================

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ language 'plpgsql';

-- 为所有包含 updated_at 字段的表创建触发器
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_user_profiles_updated_at BEFORE UPDATE ON user_profiles FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_user_settings_updated_at BEFORE UPDATE ON user_settings FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_user_likes_updated_at BEFORE UPDATE ON user_likes FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_user_follows_updated_at BEFORE UPDATE ON user_follows FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_user_matches_updated_at BEFORE UPDATE ON user_matches FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_topics_updated_at BEFORE UPDATE ON topics FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_posts_updated_at BEFORE UPDATE ON posts FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_comments_updated_at BEFORE UPDATE ON comments FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_groups_updated_at BEFORE UPDATE ON groups FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_group_members_updated_at BEFORE UPDATE ON group_members FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_conversations_updated_at BEFORE UPDATE ON conversations FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_vip_plans_updated_at BEFORE UPDATE ON vip_plans FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_vip_orders_updated_at BEFORE UPDATE ON vip_orders FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_user_coin_accounts_updated_at BEFORE UPDATE ON user_coin_accounts FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_task_configs_updated_at BEFORE UPDATE ON task_configs FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_user_tasks_updated_at BEFORE UPDATE ON user_tasks FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_shop_items_updated_at BEFORE UPDATE ON shop_items FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_shop_orders_updated_at BEFORE UPDATE ON shop_orders FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_user_items_updated_at BEFORE UPDATE ON user_items FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_invite_codes_updated_at BEFORE UPDATE ON invite_codes FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_invite_records_updated_at BEFORE UPDATE ON invite_records FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_mining_pools_updated_at BEFORE UPDATE ON mining_pools FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_staking_records_updated_at BEFORE UPDATE ON staking_records FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_activities_updated_at BEFORE UPDATE ON activities FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_user_activity_records_updated_at BEFORE UPDATE ON user_activity_records FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_daily_user_stats_updated_at BEFORE UPDATE ON daily_user_stats FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================
-- 完成！
-- ============================================

